clear all
set more off , permanently
capture log close
set scheme s1color
graph set window fontface "Garamond"

*******************************************************************************************************
******** This sheet plots distributions of price notifications and changes pre- and post MTU **********
*******************************************************************************************************
	
		
***************************************************************
*** Figure B1: Distribution of fuel stations across Germany ***
***************************************************************		

// Load stations coordinates: Germany
cd "$dta"
use stations.dta , clear

// Keep relevant variables
keep id_data latitude longitude
gen longitude_map = longitude* 0.64124151 

// Save
cd"$dta"
compress
save "stations_coordinates_germany.dta" , replace



// Load coordinates of Germany 
shp2dta using "$raw/05_Coordinates/DEU_adm3.shp" ,  database("$raw/DEU_adm3_database.dta") coordinates("$raw/DEU_adm3_coord.dta") replace 

//	GEO data: save mercador projection
	use "$raw/05_Coordinates/DEU_adm3_coord.dta", clear
	replace _X = _X * 0.64124151 // Adjustment for map:cos(lat_mean*0.0174533); cos(50.11552*0.0174533)
	save "$raw/05_Coordinates/DEU_adm3_coord_map.dta", replace
	
	
	
//	Gen Figure 10: Distribution of fuel stations across Germany
use "$raw\05_Coordinates\DEU_adm3_database.dta", clear

spmap using "$raw\05_Coordinates\DEU_adm3_coord_map.dta" , ///
	clmethod(unique) ///
	id(_ID) legend(size(small)) ///
	point(data("$dta/stations_coordinates_germany.dta") ///
	xcoord(longitude_map) ycoord(latitude) fcolor(black)  ocolor(black) size(*0.3) legenda(off))  
	
graph export "$output/Figure_B1_fuel stations DE_bw.pdf", replace
	
	
	
// Erase data 
erase "$dta\stations_coordinates_germany.dta"
erase "$raw/05_Coordinates/DEU_adm3_coord_map.dta"

		

**********************************************************************
*** Figure B2: Number of fuel stations with positive price reports ***
**********************************************************************


	// Load German data
		cd "$dta"
		use germany_prices_5pm.dta, clear
		sort date

	// Only keep price notifications after 1 Jan 2013
		drop if date <= d(01jan2013)
		
	// Generate number of daily reports
		gen n_reports=0
		replace n_reports=1 if e5_17oclock_panel!=. 
	
	// Collapse data
		collapse (sum) n_reports , by(date)

	// Replace n_reports between 12 September 2013 and 01 October 2013 as missing
		replace n_reports = . if date > d(12sep2013) & date < d(01oct2013)
		

	// Gen Figure B2: Number of fuel stations with positive price reports at 5pm
		twoway (line n_reports date, lwidth(thin) lpattern(solid) color(blue*1.25) cmissing(n)) ,  ytitle("Number of reporting petrol stations") legend(off) ///
			xtitle("") xlabel(`=d(01feb2013)' "February 2013"  `=d(01jul2013)' "July 2013"  `=d(01dec2013)' "December 2013" `=d(01may2014)' "May 2014" `=d(01oct2014)' "October 2014") ///
			ylabel(5000 "5,000" 10000 "10,000" 15000 "15,000", nogrid) bgcolor(white) ysize(7) xsize(15) yscale(range(0 15000)) scale(1.2) 
			
		graph export "$output\Figure_B2_number_gasstations_17oclock.pdf" , replace

	* PDF Format for JPE Micro
		twoway (line n_reports date, lwidth(thin) lpattern(solid) color(blue*1.25) cmissing(n)) ,  ytitle("Number of reporting petrol stations") legend(off) ///
			xtitle("") xlabel(`=d(01feb2013)' "February 2013"  `=d(01jul2013)' "July 2013"  `=d(01dec2013)' "December 2013" `=d(01may2014)' "May 2014" `=d(01oct2014)' "October 2014") ///
			ylabel(5000 "5,000" 10000 "10,000" 15000 "15,000", nogrid) bgcolor(white) ysize(7) xsize(15) yscale(range(0 15000)) scale(1.2) 
			
		graph export "$output\Figure_B2_number_gasstations_17oclock.pdf" , replace

********************************************** 
** Figure B3: Number of daily price changes **
********************************************** 

	* Get data
		use "$dta\price_notifications.dta" , clear

	* Keep relevant variables and sort data
		keep id_data date clock e5 diesel
		keep if date > d(01jan2013) & date <= d(31dec2014)

	
	* Drop diesel price	
		drop diesel
		
	* Keep non-missing 
		drop if e5==.
		sort id_data date clock

	* Gen dummy that is 1 if an observation is a price change
		gen n_chg_e5=0
		by id_data: replace n_chg_e5=1 if e5[_n]!=e5[_n-1]

	* Sum price changes by station per date and drop if these are absolute outliers (probably technical error)
		bysort id_data date: gegen total_changes = sum(n_chg_e5)
		hist total_changes if total_changes < 100 , frac
		drop if total_changes > 20
		
	* Collapse number of changes by date
		gcollapse (sum) n_chg_e5 , by(date)

	* No price data for 13 Sep - 20 Sep 2013
		replace n_chg_e5 = . if date > d(12sep2013) & date < d(01oct2013)
		
		gen one=1
		tsset one date 
		tsfill, full
		
	* Gen Figure 12: Number of daily price changes (based on petrol prices)
		twoway (line n_chg_e5 date, lwidth(thin) lpattern(solid) color(blue*1.25) cmissing(n)) , ///
		/*title("Total number of price changes per day")*/  ytitle("Number of reported price changes") legend(off) ///
			xtitle("") xlabel(`=d(01feb2013)' "February 2013"  `=d(01jul2013)' "July 2013"  `=d(01dec2013)' "December 2013" `=d(01may2014)' "May 2014" `=d(01oct2014)' "October 2014") ///
		ylabel(20000 "20,000" 40000 "40,000" 60000 "60,000" 80000 "80,000") bgcolor(white) ysize(7) xsize(15) yscale(range(0 15000)) scale(1.2) xline(`=d(12sep2013)', lc(gs2))
		
		graph export "$output\Figure_B3_total_number_price_change.pdf" , replace
		
		
		
*************************************************************** 
******** Figure B4: Notification patterns over the day ********
*************************************************************** 

// Load price notifications data 

	* Load price panel
		use "$dta\price_notifications.dta" , clear
		
	* Only keep relevant variables
		keep id_data date hour time e5 diesel
		sort date time
		
// Generate Dummies for time periods

	* Restrict analysis to date>=01.01.13 & 
		keep if date>=date("20130101","YMD") & date<=date("20141231","YMD")
		
	* Generate Dummies for post-MTU periods (post-MTU1: 01.10.13-31.12.14)
		gen post_MTU1 = 1 if date > date("20130930","YMD")
		replace post_MTU1 = 0 if post_MTU1 ==.
		
	* Formate time
		format time %tcHH:MM
		
	* Keep only notifications for petrol	
		drop diesel
		keep if e5!=.	
		
		
// Produce Figure 13 (based on petrol price) //
		
	* Figure 13, panel a: Notification patterns over the day
		histogram time if post_MTU1==0, percent xlabel(1000 "12 am" 10800000 "3 am" 21600000 "6 am" 32400000 "9 am" 43200000 "12 pm" 54000000 "3 pm" 64900000 "6 pm" 75600000 "9 pm", labsize(medsmall) angle(forty_five)) ///
		ylabel(, labsize(medsmall)) ytitle("Percent", margin(small) size(medlarge)) bgcolor(white) ysize(15) xsize(20) xtitle("") xscale(range(21600000 86300000)) bin(16) scale(1.2)
	
		graph export "$output\Figure_B4_a_petrol_price_notifications_pre-MTU1_90min.pdf" , replace			
		
	* Figure 13, panel b: Notification patterns over the day
		histogram time if post_MTU1==1, percent xlabel(1000 "12 am" 10800000 "3 am" 21600000 "6 am" 32400000 "9 am" 43200000 "12 pm" 54000000 "3 pm" 64900000 "6 pm" 75600000 "9 pm", labsize(medsmall) angle(forty_five)) ///
		ylabel(, labsize(medsmall)) ytitle("Percent", margin(small) size(medlarge)) bgcolor(white) ysize(15) xsize(20) xtitle("") xscale(range(21600000 86300000)) bin(16) scale(1.2)
	
		graph export "$output\Figure_B4_b_petrol_price_notifications_post-MTU1_90min_incl_test.pdf" , replace		




********************************************************
******** Figure B5: Daily fuelling patterns ************
********************************************************

// Import and clear data of hours collapsed
	*Import excel sheet with scanned data of "Abbildung 11 of Bundestagsbericht 19/3693"
		cd "$raw"
		import excel "02_Other_Datasets\Abbildung11_Bundestagsbericht_daten.xlsx", cellrange(F1:G8) firstrow clear

	*Rename variables
		rename Time time

	* Gen ordering variable
		gen pepe = 1
		replace pepe = 2 if time == "11:00-13:00"
		replace pepe = 3 if time == "13:00-15:00"
		replace pepe = 4 if time == "15:00-17:00"
		replace pepe = 5 if time == "17:00-19:00"
		replace pepe = 6 if time == "19:00-21:00"
		replace pepe = 7 if time == ">21:00"

// Generate Figure 15: Daily fuelling patterns
		Graph bar consumer_share, over(pepe, relabel(1 "<11 am" 2 "11 am - 1 pm" 3 "1 pm - 3 pm" 4 "3 pm - 5 pm" 5 "5 pm - 7 pm" 6 "7 pm - 9 pm" 7 "> 9 pm") lab(labsize(medsmall) angle(60)) gap(50)) ytitle("Share of consumers refueling at hour t", margin(small) size(medsmall)) ///
		bgcolor(white) ysize(10) xsize(18) ylabel(,labsize(small)) scale(1.2)
	
		graph export "$output\Figure_B5_share_consumers_refueling_1.pdf" , replace




****************************************
***  Figure B6: Daily price patterns ***
****************************************		

// Load and clear data
	* Load price panel
		use "$dta\price_panel.dta" , clear
		
	* Restrict analysis to date>=01.10.13 & <=31.12.14
		keep if date>=date("20131001","YMD") & date<=date("20141231","YMD")
	
	* Drop unnecessary variables
		drop source *panel dow weekend e5_opening
					
	* Save temp
		cd "$dta"
		compress
		save price_panel_mod.dta , replace
		
// Calculate means of prices by hour for 01.10.13-31.12.14 and plot Figure 16a
	use "$dta\price_panel_mod.dta" , clear
	* Collapse prices by hour, mean 
		gcollapse (mean) e5_7oclock e5_8oclock e5_9oclock e5_10oclock e5_11oclock ///
		e5_12oclock e5_13oclock e5_14oclock e5_15oclock e5_16oclock e5_17oclock e5_18oclock ///
		e5_19oclock e5_20oclock
		
	* Reshape from wide to long
		gen aux=1
		reshape long e5_, i(aux) j(time) string
		drop aux
		
	* Gen string time
		split time , p(o)
		destring time1 , replace
	
	* Graph Bar
		twoway (bar e5_ time1) , xscale(range(7 20)) yscale(range(1.47 1.57)) ytitle("Average gasoline price (Euro)", margin(small) size(large)) ///
		bgcolor(white) ysize(10) xsize(18) ylabel(1.48 (0.02) 1.56,labsize(medium)) ///
		xlabel(7 "7 am" 8 "8 am" 9 "9 am" 10 "10 am" 11 "11 am" 12 "12 pm" 13 "1 pm" 14 "2 pm" 15 "3 pm" 16 "4 pm" 17 "5 pm" 18 "6 pm" 19 "7 pm" 20 "8 pm", labsize(medium) angle(45)) xtitle("") scale(1.2)
	
		graph export "$output\Figure_B6a_daily_pricing_pattern_1.pdf" , replace
		
		
// Calculate means of prices by hour for 01.10.13-31.12.14 and plot Figure 16b
	use "$dta\price_panel_mod.dta" , clear
	* Collapse prices by hour, mean 
		gcollapse (mean) gazole_7oclock gazole_8oclock gazole_9oclock gazole_10oclock gazole_11oclock ///
		gazole_12oclock gazole_13oclock gazole_14oclock gazole_15oclock gazole_16oclock gazole_17oclock gazole_18oclock ///
		gazole_19oclock gazole_20oclock
		
	* Reshape from wide to long
		gen aux=1
		reshape long gazole_, i(aux) j(time) string
		drop aux
		
	* Gen string time
		split time , p(o)
		destring time1 , replace
	
	* Graph Bar
		twoway (bar gazole_ time1) , xscale(range(7 20)) yscale(range(1.31 1.39)) ytitle("Average diesel price (Euro)", margin(small) size(large)) ///
		bgcolor(white) ysize(10) xsize(18) ylabel(1.32 (0.02) 1.38,labsize(medium)) ///
		xlabel(7 "7 am" 8 "8 am" 9 "9 am" 10 "10 am" 11 "11 am" 12 "12 pm" 13 "1 pm" 14 "2 pm" 15 "3 pm" 16 "4 pm" 17 "5 pm" 18 "6 pm" 19 "7 pm" 20 "8 pm", labsize(medium) angle(45)) xtitle("") scale(1.2)
	
		graph export "$output\Figure_B6b_diesel_daily_pricing_pattern_1.pdf" , replace		

	
erase "$dta\price_panel_mod.dta"
			